# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
from ttzScrapyProject.items import *
import mysql.connector


class TtzscrapyprojectPipeline:
    def process_item(self, item, spider):
        print(f"正在执行入库中{item}")
        return item


class TtzscrapyprojectPipelineLog:
    def process_item(self, item, spider):
        print(f"正在打印日志{item}")
        return item


class TtzscrapyprojectPipelineMysql:
    def __init__(self):
        self.db = mysql.connector.connect(
            host="101.43.218.147",
            user="root",
            password="8130ff082fd47818",
            database="scrapy_user_game",
            port="53306"
        )
        self.cursor = self.db.cursor()

    def process_item(self, item, spider):
        # Insert data into the database
        # sql = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
        # values = (item['value1'], item['value2'])
        # self.cursor.execute(sql, values)
        # self.db.commit()

        if isinstance(item,UserItem):
            sql = "update user set user_token = %s where user_phone = %s"
            values = (item['userToken'], item['userPhone'])
            self.cursor.execute(sql, values)

        if isinstance(item,LotterInfoItem):
            sql = f"select * from lottery_info where lottery_id ={item['lotteryId']}"
            lotteryId = item['lotteryId']
            # values1 = (item['lotteryId'],item['lotteryId'])
            self.cursor.execute(sql)
            result  = self.cursor.fetchall()
            if result :
                print(f"期号{lotteryId}已入库,无需操作,跳过插入")
            else:
                sql = "INSERT INTO `lottery_info` (`lottery_id`, `lottery_luck_num1`, `lottery_luck_num2`, `lottery_luck_num3`, `lottery_luck_num`, `lottery_open_time`)" \
                      "VALUES (%s, %s, %s, %s, %s, %s)"
                values = (item['lotteryId'], item['lotteryLuckNum1'], item['lotteryLuckNum2'], item['lotteryLuckNum3'], item['lotteryLuckNum'], item['lottery_open_time']),

                self.cursor.executemany(sql, values)

             # rowcount = cursor.rowcount
            #
            # if rowcount:
            # # There are results
            # else:
            #     # There are no results


        self.db.commit()
        return item

    def close_spider(self, spider):
        print(f"数据库连接关闭")
        self.cursor.close()
        self.db.close()


class TtzscrapyprojectPipelineWriteFile:
    def process_item(self, item, spider):
        if isinstance(item,UserItem):
            # 将数据写入到文件中
            with open('userItem.txt', 'w') as file:
                file.write(f"{item['nickname']},{item['coinBean']},{item['userId']}\n")
            return item
        if isinstance(item,LotterInfoItem):
            with open('lotterInfoItem.txt', 'w',encoding="utf-8") as file:
                file.write(f"{item['lotteryId']},{item['lotteryLuckNum1']},{item['lotteryLuckNum2']},{item['lotteryLuckNum3']},{item['lotteryLuckNum']},{item['totalSum']},{item['luckNumRate']},{item['lottery_transfer_time']},{item['transferAmount']},{item['isTransfer']}\n")
            return item